package com.cms.biz.rsf;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import com.cms.bean.RSFBean;
import com.cms.config.CategoryTypeDef;
import com.cms.config.ConfigDef;
import com.cms.database.DBHelper;
import com.cms.facade.RequestFacade;
import com.cms.facade.SessionFacade;
import com.cms.framework.Command;
import com.cms.framework.CommandException;
import com.cms.util.StringHelper;

public class GetRSFListCommand implements Command {
	private String next;

	public GetRSFListCommand(String next) {
		this.next = next;
	}

	@Override
	public String execute(HttpServletRequest req) throws CommandException {

		HttpSession session = req.getSession();
		Boolean isAdmin = SessionFacade.isSysAdmin(session)||true;
		Connection conn = DBHelper.getConn();
		PreparedStatement pStatement = null;
		ResultSet rSet = null;
		PreparedStatement pst_count = null;
		ResultSet rs_count = null;
		StringBuffer sqlBuffer = null;
		try {
			String userId = SessionFacade.getUser(session);
			int folderId = StringHelper.isRequestParamEmptyInt(req, "folder_id", ConfigDef.ACTIVE);
			List<RSFBean> list = new ArrayList<RSFBean>();
			int page = 1;
			String pageStr = req.getParameter("page");
			if (pageStr != null) {
				page = Integer.parseInt(pageStr);
			}
			int num_per_page = ConfigDef.NUM_OF_ITEMS_PER_PAGE;
			String num_per_page_str = req.getParameter("num_per_page");
			if (num_per_page_str != null) {
				num_per_page = Integer.parseInt(num_per_page_str);
			}

			int categoryId = StringHelper.isRequestParamEmptyInt(req,
					"categoryTypeId", CategoryTypeDef.NEW);

			if (isAdmin) {
				sqlBuffer = new StringBuffer(SQLString.GET_RSF_ALL);
			} else {
				sqlBuffer = new StringBuffer(SQLString.GET_RSF_BY_USER);
			}

			StringBuffer whereBuffer = new StringBuffer();
			String title = req.getParameter("adv_title");
			if (!StringHelper.isEmpty(title)) {
				whereBuffer.append(" and r.title like '%" + title + "%' ");
			}

			String subCategoryId = req.getParameter("adv_subcategory");
			if (!StringHelper.isEmpty(subCategoryId)&&!subCategoryId.equals("-1")) {
				whereBuffer.append(" and r.sub_category_id = " + subCategoryId);
			}

			String startDate = req.getParameter("adv_startDate");
			if (!StringHelper.isEmpty(startDate)) {
				whereBuffer.append(" and r.create_date >= '" + startDate
						+ " 00:00:00' ");
			}

			String endDate = req.getParameter("adv_endDate");
			if (!StringHelper.isEmpty(endDate)) {
				whereBuffer.append(" and r.create_date <= '" + endDate
						+ " 23:59:59' ");
			}

			sqlBuffer.append(whereBuffer);

			String sortCol = req.getParameter("sortCol");
			if (sortCol == null)
				sortCol = " r.create_date ";
			String sortDir = req.getParameter("sortDir");
			if (sortDir == null)
				sortDir = " desc";

			StringBuffer orderBuf = new StringBuffer("");
			orderBuf.append(sortCol);
			orderBuf.append(" ");
			orderBuf.append(sortDir);

			String sql = DBHelper.getPagingSQL(sqlBuffer.toString(), orderBuf.toString());

			pStatement = conn.prepareStatement(sql);
System.out.println(sql);
			if (isAdmin) {
				pStatement.setInt(1, folderId);
				pStatement.setInt(2, categoryId);
				pStatement.setInt(3, (page-1)*num_per_page);
				pStatement.setInt(4, num_per_page);
			} else {
				pStatement.setInt(1, folderId);
				pStatement.setInt(2, categoryId);
				pStatement.setString(3, userId);
				pStatement.setInt(4, (page-1)*num_per_page);
				pStatement.setInt(5, num_per_page);
			}

			rSet = pStatement.executeQuery();
			while (rSet.next()) {
				RSFBean rsfBean = new RSFBean();
				rsfBean.setId(rSet.getInt("id"));
				rsfBean.setFolderId(rSet.getInt("folder_id"));
				rsfBean.setCategoryId(rSet.getInt("category_id"));
				rsfBean.setSubCategoryId(rSet.getInt("sub_category_id"));
				rsfBean.setSubCategoryName(rSet.getString("category_name"));
				rsfBean.setSpecialId(rSet.getInt("special_id"));
				rsfBean.setTitle(rSet.getString("title"));
				rsfBean.setTitleColor(rSet.getString("title_color"));
				rsfBean.setAuthor(rSet.getString("author"));
				rsfBean.setSource(rSet.getString("source"));
				rsfBean.setCount(rSet.getInt("count"));
				rsfBean.setIsTop(rSet.getInt("istop"));
				rsfBean.setIsImg(rSet.getInt("isimg"));
				rsfBean.setImgName(rSet.getString("img_name"));
				rsfBean.setIsPassd(rSet.getInt("ispassd"));
				rsfBean.setKeyWord(rSet.getString("keywords"));
				rsfBean.setcDate(rSet.getTimestamp("create_date"));
				rsfBean.setcUser(rSet.getString("create_user"));
				list.add(rsfBean);
			}

			//get results count
			int count = 0;
			StringBuffer countBuffer = null;
			if(isAdmin){
				countBuffer = new StringBuffer(SQLString.GET_RSF_ALL_COUNT);
			}else{
				countBuffer = new StringBuffer(SQLString.GET_RSF_BY_USER_COUNT);
			}
			countBuffer.append(whereBuffer);
			pst_count = conn.prepareStatement(countBuffer.toString());
			if (isAdmin) {
				pst_count.setInt(1, folderId);
				pst_count.setInt(2, categoryId);
			} else {
				pst_count.setInt(1, folderId);
				pst_count.setInt(2, categoryId);
				pst_count.setString(3, userId);
			}
			rs_count = pst_count.executeQuery();
			while(rs_count.next()){
				count = rs_count.getInt(1);
			}
			
			
			RequestFacade.setResultList(req, list);
			RequestFacade.setResultTotal(req, count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs_count != null) {
				try {
					rs_count.close();
					rs_count = null;
				} catch (final SQLException e) {
				}
			}
			if (pst_count != null) {
				try {
					pst_count.close();
					pst_count = null;
				} catch (final SQLException e) {
				}
			}
			if (rSet != null) {
				try {
					rSet.close();
					rSet = null;
				} catch (final SQLException e) {
				}
			}
			if (pStatement != null) {
				try {
					pStatement.close();
					pStatement = null;
				} catch (final SQLException e) {
				}
			}
			DBHelper.closeConn(conn);
		}

		return next;
	}

}
